//
//  DataBaseHandler.m
//  UI21_SQLite
//
//  Created by dllo on 16/1/11.
//  Copyright © 2016年 dllo. All rights reserved.
//

#import "DataBaseHandler.h"
#import <sqlite3.h>
#import "ModelForSection1.h"
@interface DataBaseHandler ()

@end

@implementation DataBaseHandler
#pragma mark - 知识点1 单例模式
+ (instancetype)shareDataBaseHandler {
    
    // 在静态区, 只初始化一次
    static DataBaseHandler *dataBase = nil;
    
    if (dataBase == nil) {
        
        dataBase = [[DataBaseHandler alloc] init];
    
    }
    
    return dataBase;
}

// 定义一个sqlite结构体(对象)
sqlite3 *db;

#pragma mark - 知识点2 打开数据库
- (void)open {
    
    // API: sqlite3_open
    /** 
     *  @param filename 数据库文件的路径(注意: 类型是c语言字符串指针)
     *
     *  @param ppDb 数据库对象(此处参数是数据库对象的地址)
     */
    
    if (db != nil) {
        NSLog(@"数据库已经打开!!!");
        return;
    }
    
    NSString *file = [[NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES) lastObject]stringByAppendingPathComponent:@"model.sqlite"];
    
    int result = sqlite3_open(file.UTF8String, &db);
    if (result == SQLITE_OK) {
        NSLog(@"数据库打开成功!");
    } else {
        NSLog(@"数据库打开失败, code: %d", result);
    }
}

#pragma mark - 知识点3 创建表格
- (void)createTable {
    
    // API sqlite3_exec
    
    // <#const char *sql#> 创建SQL语句
    NSString *createTableSQL = @"CREATE TABLE IF NOT EXISTS lanou1130(number INTEGER PRIMARY KEY AUTOINCREMENT, cover_image_s text, index_title text, name text, spot_id text)";

    int result = sqlite3_exec(db, createTableSQL.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"创建表格成功!");
    } else {
        NSLog(@"创建表格失败. code: %d", result);
    }
    
}

#pragma mark - 知识点4 插入数据
- (void)insertModel:(ModelForSection1 *)model {
    
    // API: sqlite3_exec
    NSString *insertSQL = [NSString stringWithFormat:@"INSERT into lanou1130(cover_image_s, index_title, name, spot_id) VALUES('%@', '%@', '%@', '%@')", model.cover_image_s, model.index_title, model.text, model.spot_id];
    int result = sqlite3_exec(db, insertSQL.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        NSLog(@"添加数据完成!");
    } else {
        NSLog(@"添加数据失败 code: %d", result);
    }
}

//#pragma mark - 知识点5 更新数据
//- (void)updataStudent:(Student *)stu forNumber:(NSInteger)number {
//    
//    // API: sqlite_exec
//    NSString *updataSQL = [NSString stringWithFormat:@"UPDATE lanou1130 SET name = '%@', sex = '%@', age = '%d' WHERE number = '%ld'", stu.name, stu.sex, stu.age, number];
//    int result = sqlite3_exec(db, updataSQL.UTF8String, NULL, NULL, nil);
//    if (result == SQLITE_OK) {
//        NSLog(@"更新成功!");
//    } else {
//        NSLog(@"更新失败 code: %d", result);
//    }
//}

#pragma mark - 知识点6 删除数据
- (void)deleteWithId:(NSString *)spot_id {
    
    // API: sqlite3_exec
    NSString *deleteSQL = [NSString stringWithFormat:@"DELETE FROM lanou1130 WHERE spot_id = '%@'", spot_id];
    int result = sqlite3_exec(db, deleteSQL.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        
        NSLog(@"删除成功!");
        NSLog(@"%@", NSHomeDirectory());
        
    } else {
        
        NSLog(@"删除失败. code: %d", result);
        
    }
    
}
- (BOOL)selectForId:(NSString *)spot_id {
    
    NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM lanou1130 WHERE spot_id = '%@'", spot_id];
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, selectSQL.UTF8String, -1, &stmt, nil);
    
    if (result == SQLITE_OK) {
        // 销毁对象.
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            
            return YES;
        }
        sqlite3_finalize(stmt);
    } else {
        NSLog(@"不能正常查询. code:%d", result);
        // 销毁对象.
        sqlite3_finalize(stmt);
        
    }
    return NO;
    
}
#pragma mark - 知识点7 查询数据
- (NSArray *)selectAll {
    
    // 存放符合条件的数据(元素是model对象)
    NSMutableArray *arr = [NSMutableArray array];
    
    // sql语句.
    NSString *selectSQL = [NSString stringWithFormat:@"SELECT * FROM lanou1130"];
    
    // API: sqlite3_stmt结构体, sqlite3_prepareV2, sqlite3_step, sqlite3_column
    
    // 声明一个stmt对象(结构体)
    sqlite3_stmt *stmt = nil;
    int result = sqlite3_prepare_v2(db, selectSQL.UTF8String, -1, &stmt, nil);
    if (result == SQLITE_OK) {
        NSLog(@"准备就绪, 查询中......");
        
        // 每行都执行查询语句.
        while (sqlite3_step(stmt) == SQLITE_ROW) {
        
            // 如果查询条件匹配. 通过sqlite3_column函数簇 取出值.
            
            // 1是取出哪一列的数据值.(从第0列开始)
            const unsigned char *cover_image_s = sqlite3_column_text(stmt, 1);
            const unsigned char *index_title = sqlite3_column_text(stmt, 2);
            const unsigned char *name = sqlite3_column_text(stmt, 3);
            const unsigned char *spot_id = sqlite3_column_text(stmt, 4);
            // 创建model对象, 赋值之后放到数组中.
            ModelForSection1 *model = [[ModelForSection1 alloc] init];
            
            model.cover_image_s = [NSString stringWithUTF8String:(const char *)cover_image_s];
            model.index_title = [NSString stringWithUTF8String:(const char *)index_title];
            model.text = [NSString stringWithUTF8String:(const char *)name];
            model.spot_id = [NSString stringWithUTF8String:(const char *)spot_id];
            [arr addObject:model];
            [model release];
        }

        // 销毁对象.
        sqlite3_finalize(stmt);
    } else {
        NSLog(@"不能正常查询. code: %d", result);
        
        // 销毁对象.
        sqlite3_finalize(stmt);
    }
    
    return arr;
}
//#pragma mark - 知识点8 删除表单
//- (void)dropTable {
//    NSString *dropSQL = @"DROP TABLE lanou1130";
//    int result = sqlite3_exec(db, dropSQL.UTF8String, NULL, NULL, nil);
//    if (result == SQLITE_OK) {
//        NSLog(@"删除表单成功!");
//    } else {
//        NSLog(@"删除表单失败 code: %d", result);
//    }
//}

#pragma mark - 知识点9 关闭数据库
- (void)closeDB {
    int result = sqlite3_close(db);
    if (result == SQLITE_OK) {
        NSLog(@"数据库关闭成功!");
        db = nil;
    } else {
        NSLog(@"数据库关闭失败. code: %d", result);
    }
}

@end
